﻿CREATE VIEW [dbo].[qryInvoice] as
select T4.OrderID, 
   T1.PackageID, 
   T1.PrintBatchID,
   T4.InvoiceDate,
   T4.ProductTotal,
   T4.Shipping,
   T4.Taxes,
   T4.OrderTotal, 
   T5.CustomerID, 
   T2.Description as Carrier, 
   T2.Abbrev as CarrierAbbrev,
   T12.LongName as OwnerLongName,
   T9.Name as BillToName, 
   dbo.AddressForDisplay(T9.AddressID) as BillToAddress,
   T6.Name as ShipToName, 
   dbo.AddressForDisplay(T1.AddressID) as ShipToAddress,
   dbo.Decrypt(T13.CardNumber) as CardNumber,
   dbo.ObfuscateAccountNumber( coalesce( dbo.Decrypt(T13.CardNumber), dbo.Decrypt(T13.BankAccountNumber) ) ) as AccountNumber,
   dbo.AccountNetwork(dbo.Decrypt(T13.AccountID)) as AccountNetwork
from tblPackage T1
	join codeCarrier T2 on T1.CarrierCode = T2.CarrierCode
	join ( select PackageID, OrderID
		from tblInventoryTransaction
		group by PackageID, OrderID ) as T3 on T1.PackageID = T3.PackageID
	join tblOrderHeader T4 on T3.OrderID = T4.OrderID
	join tblCustomer T5 on T4.CustomerID = T5.CustomerID
	join tblPerson T6 on T1.PersonID = T6.PersonID 				-- Ship-To Person
	join tblPerson T9 on T5.PersonID = T9.PersonID				-- Bill-To Person
	join tblOwner T12 on T4.OwnerID = T12.OwnerID
	left outer join tblAccount T13 on T4.AccountID = T13.AccountID
